﻿using IWshRuntimeLibrary;
using RandomMobileNumber.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;


namespace RandomMobileNumber
{
    /// <summary>
    /// 数据操作等
    /// </summary>
    public class AppService
    {
        public static List<string> Load()
        {
            var file = GetSetting("dataFile");
            if (string.IsNullOrEmpty(file)) return null;

            var result = new List<string>();

            using (System.IO.FileStream fs = new System.IO.FileStream(file, System.IO.FileMode.Open, System.IO.FileAccess.Read))
            {
                try
                {
                    var ds = Excel.ExcelReaderFactory.CreateOpenXmlReader(fs).AsDataSet();
                    var table = ds.Tables[0];

                    foreach (DataRow r in table.Rows)
                    {
                        var number = r[0].ToString();
                        result.Add(number);
                    }
                }
                catch (Exception)
                {
                    throw;
                }
            }

            return result;
        }

        /// <summary>
        /// Excel数据导入
        /// </summary>
        /// <returns></returns>
        public static bool ImportExcelToDatabase()
        {
            List<string> newNumbers = Load();

            if (newNumbers.Count.Equals(0)) return true;

            var oldNumbers = from n in GetNumbers()
                             select n.number;

            List<Number> Numbers = new List<Number>();

            foreach (string number in newNumbers.Except(oldNumbers))
            {
                Numbers.Add(new Number { number = number, soldDate = Convert.ToDateTime("1970.1.1") });
            }

            return App.SqlClient.SqlBulkCopy(Numbers);
        }

        /// <summary>
        /// 获取所有Number
        /// </summary>
        /// <returns></returns>
        public static List<Number> GetNumbers()
        {
            var sql = @"select * from Number";
            return App.SqlClient.GetList<Number>(sql);
        }


        /// <summary>
        /// 获取配置文件值
        /// </summary>
        /// <param name="app_setting_name"></param>
        /// <returns></returns>
        public static string GetSetting(string app_setting_name)
        {
            bool hasKey = ConfigurationManager.AppSettings.AllKeys.Count(k => k.Equals(app_setting_name)).Equals(1);
            if (hasKey)
                return ConfigurationManager.AppSettings.Get(app_setting_name);
            return null;
        }

        public static string GetConnectionString(string db_key)
        {
            string result = null;
            try
            {
                result = ConfigurationManager.ConnectionStrings[db_key].ConnectionString;
            }
            catch (Exception)
            {
                return "未能获取数据库链接字符串";
            }

            return result;

        }

        public static void CreateShortcut(string title)
        {
            string link = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
                + Path.DirectorySeparatorChar + title + ".lnk";
            var shell = new WshShell();
            var shortcut = shell.CreateShortcut(link) as IWshShortcut;

            shortcut.TargetPath = System.Reflection.Assembly.GetExecutingAssembly().Location;
            shortcut.WorkingDirectory = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
            //shortcut...

            shortcut.Save();
        }

        /// <summary>
        /// 清表
        /// </summary>
        internal static void ClearTable()
        {
            var sql = @"delete from Number";
            App.SqlClient.ExecuteCommand(sql);
        }
    }
}
